package mudbconnection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;
import muobject.Physical;
import muobject.Student;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component("DBPhysicalsService")
public class DBPhysicalsService implements DBPhysicals {
	private JdbcTemplate jdbct;

	@Autowired
	public DBPhysicalsService(DataSource jdbct) {
		super();
		this.jdbct = new JdbcTemplate(jdbct);
	}
	
	public List<Physical> getAllPhysicals(){
		try{
			@SuppressWarnings("unchecked")
			List<Physical> classo = (List<Physical>)jdbct.query("Select * from Physicals ", new PhysicalMapper());
			return classo;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public List<Physical> getSpicalStudentPhysicals(String stno, String year){
		try{
			@SuppressWarnings("unchecked")
			List<Physical> physical = (List<Physical>)jdbct.query("Select * from Physicals where Stno=? and Years=?", 
					new Object[]{ stno, year },
					new PhysicalMapper());
			return physical;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public Physical getSpicalStudentYearTestdatePhysicals(String stno, String year, String testDate){
		try{
			Physical physical = (Physical)jdbct.queryForObject("Select * from Physicals where Stno=? and Years=? and TestDate=?", 
					new Object[]{ stno, year, testDate }, new PhysicalMapper());
			return physical;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	@SuppressWarnings("rawtypes")
	public List getAllTestDate(String sno, String year,
			String grade, String sclass) {
		try {
			List testDate = jdbct
					.queryForList("Select TestDate " +
							"from Students s left join Physicals p " +
							"on s.stno = p.stno " +
							"where p.Years like ? and s.Grade like ? and s.sclass like ? and s.sno=? " +
							"group by TestDate " +
							"order by TestDate desc ", 
							new Object[] { year, grade, sclass, sno });
			return testDate;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public boolean setPhysical(Physical physical) {
		try{
			jdbct.update("insert into Physicals(stno, Years, TestDate, Height, Weight, Grip, Jump, Situp, Vcapacity, run69, run15, Block) " +
					"values(?,?,?,?,?,?,?,?,?,?,?,?)", 
				     new Object[] {physical.getStno(), physical.getYears(), String.valueOf(physical.getTestDate()), physical.getHeight(),
					physical.getWeight(), physical.getGrip(), physical.getJump(),
					physical.getSitup(), physical.getVcapacity(), physical.getRun69(), physical.getRun15(), physical.getBlock()});
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public boolean updatePhysical(Physical physical){
		try{
			jdbct.update("update Physicals set Height=?, Weight=?, Grip=?, Jump=?, Situp=?, Vcapacity=?, " +
					"run69=?, run15=?, Block=? where stno=? and Years=? and TestDate=?", 
				     new Object[] {physical.getHeight(), physical.getWeight(), physical.getGrip(), physical.getJump(),
					physical.getSitup(), physical.getVcapacity(), physical.getRun69(), physical.getRun15(), physical.getBlock(), physical.getStno(),
					physical.getYears(), physical.getTestDate()});
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}

	public boolean deletePhysical(String stno, String year, String testDate){
		try{
			jdbct.update("delete from Physicals where stno=? and Years=? and TestDate=?", 
				     new Object[] {stno, year, testDate});
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	class PhysicalMapper implements RowMapper{
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
			Physical physical = null;
			try{
				physical = new Physical(rs.getInt("Pid"), rs.getString("Stno"), rs.getString("Years")
						,rs.getDate("TestDate"), rs.getDouble("Height"), rs.getDouble("Weight")
						,rs.getDouble("Grip"), rs.getDouble("Jump"), rs.getDouble("Situp")
						,rs.getDouble("Vcapacity"), rs.getDouble("run69"), rs.getDouble("run15"), rs.getString("Block"), rs.getDate("Createdate")
						,rs.getDate("Updatedate"));
			}catch(Exception e){
				System.out.println(e.getMessage());
			}
			return physical;
		}
	}
}
